﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using Framework;

namespace Enterprise
{
    public class TermService : BaseService
    {
        public DataRow GetById(int id)
        {
            return DbHelperOleDb.Query("select * from tbterm where cotermid={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(int pageValue = 1, int rowsValue = 10, string sort = "CoSort", string order = "Desc", string name = "")
        {
            if (sort.IsNullOrEmpty())
            {
                sort = "CoSort";
            }
            if (order.IsNullOrEmpty())
            {
                order = "Desc";
            }
            if (pageValue < 1)
            {
                pageValue = 1;
            }
            if (rowsValue < 1)
            {
                rowsValue = 10;
            }
            var parameters = new[]
                {
                    new OleDbParameter("@CoName", OleDbType.VarChar, 255)
                };
            parameters[0].Value = name.SqlLike();
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where (CoName like '%'+@CoName+'%'  or  CoNameEn like '%'+@CoName+'%')");
            int total = DbHelperOleDb.GetSingle("select count(*) from TbTerm {0}".FormatWith(sbSqlWhere), parameters).SafeToInt();
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select top {0} * from TbTerm {1} ", rowsValue, sbSqlWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(
                    " and CoTermId not in (select top {0} CoTermId from TbTerm {1} order by {2} {3},cotermid desc)  ",
                    rowsValue * (pageValue - 1), sbSqlWhere, sort, order);
            sbSql.AppendFormat(" order by {0} {1},cotermid desc ", sort, order);
            DataSet ds = DbHelperOleDb.Query(sbSql.ToString(), parameters);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total,
                DataTable = ds.Tables["ds"]
            };
            return table;
        }

        public int Add(string name = "", string nameEn = "", string content = "", string contentEn = "", DateTime? date = null, int sort = 0, bool isDelete = false)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("insert into  TbTerm(");
            sbSql.Append("CoName,CoNameEn,CoContent,CoContentEn,CoDate,CoSort,CoIsDelete");
            sbSql.Append(") values (");
            sbSql.Append("@CoName,@CoNameEn,@CoContent,@CoContentEn,@CoDate,@CoSort,@CoIsDelete");
            sbSql.Append(")");
            var parameters = new[]
                {
                    new OleDbParameter("@CoName", OleDbType.VarChar,255),
                    new OleDbParameter("@CoNameEn", OleDbType.VarChar,255),
                    new OleDbParameter("@CoContent", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoContentEn", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoDate", OleDbType.Date),
                    new OleDbParameter("@CoSort", OleDbType.Integer),
                    new OleDbParameter("@CoIsDelete", OleDbType.Boolean)
                };
            parameters[0].Value = name;
            parameters[1].Value = nameEn;
            parameters[2].Value = content;
            parameters[3].Value = contentEn;
            parameters[4].Value = date ?? DateTime.Now;
            parameters[5].Value = sort;
            parameters[6].Value = isDelete;
            var newId = DbHelperOleDb.Add(sbSql.ToString(), parameters);
            return newId;
        }
        public int Edit(int id = 0, string name = "", string nameEn = "", string content = "", string contentEn = "", DateTime? date = null, int sort = 0, bool isDelete = false)
        {
            var sbSql = new StringBuilder();
            sbSql.Append(" update TbTerm set ");
            sbSql.Append(" CoName=@CoName,CoNameEn=@CoNameEn,CoContent=@CoContent,CoContentEn=@CoContentEn,CoDate=@CoDate,CoSort=@CoSort");
            sbSql.AppendFormat("  where CoTermId={0}", id);
            var parameters = new[]
                {
                    new OleDbParameter("@CoName", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoNameEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoContent", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoContentEn", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoDate", OleDbType.Date),
                    new OleDbParameter("@CoSort", OleDbType.Integer)
                };
            parameters[0].Value = name;
            parameters[1].Value = nameEn;
            parameters[2].Value = content;
            parameters[3].Value = contentEn;
            parameters[4].Value = date ?? DateTime.Now;
            parameters[5].Value = sort;
            var count = DbHelperOleDb.Add(sbSql.ToString(), parameters);
            return count;
        }

        public int Delete(int id)
        {
            return DbHelperOleDb.ExecuteSql("delete from TbTerm where CoTermId={0}".FormatWith(id)).SafeToInt();
        }

    }
}
